package com.gaattc.dao;

import com.gaattc.model.Goods;
import com.gaattc.model.GoodsSpecs;
import com.gaattc.model.User;
import com.gaattc.model.bo.*;
import com.gaattc.model.vo.*;
import com.gaattc.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang3.StringUtils;

import java.sql.Date;
import java.sql.SQLException;
import java.util.*;


public class OrderDaoImpl implements OrderDao {

    private Map getDynamicSqlAndParams(OrdersByPageBO ordersByPageBO) {
        String baseSql = " where 1 = 1 ";
        List params = new ArrayList();
        if(!StringUtils.isEmpty(ordersByPageBO.getAddress())){
            baseSql += " and address like ? ";
            params.add("%" + ordersByPageBO.getAddress() + "%");
        }
        if(!StringUtils.isEmpty(ordersByPageBO.getGoods())){
            baseSql += " and goods like ? ";
            params.add("%" + ordersByPageBO.getGoods() + "%");
        }
        if(!StringUtils.isEmpty(ordersByPageBO.getMoneyLimit1())){
            baseSql += " and amount <= ? ";
            params.add(ordersByPageBO.getMoneyLimit1());
        }
        if(!StringUtils.isEmpty(ordersByPageBO.getMoneyLimit2())){
            baseSql += " and amount >= ? ";
            params.add(ordersByPageBO.getMoneyLimit2());
        }
        if(!StringUtils.isEmpty(ordersByPageBO.getId())){
            baseSql += " and id = ? ";
            params.add(ordersByPageBO.getId());
        }
        if(!StringUtils.isEmpty(ordersByPageBO.getName())){
            baseSql += " and nickname like ? ";
            params.add("%" + ordersByPageBO.getName() + "%");
        }
        if(ordersByPageBO.getState() != -1){
            baseSql += " and stateId = ? ";
            params.add(ordersByPageBO.getState());
        }
        HashMap<Object, Object> map = new HashMap<>();
        map.put("sql", baseSql);
        map.put("params", params);
        return map;
    }

    @Override
    public Integer getTotalCount(OrdersByPageBO ordersByPageBO) {

        Map map = getDynamicSqlAndParams(ordersByPageBO);
        String sql = (String) map.get("sql");
        List params = (List) map.get("params");
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        //查询count值，jdbc返回的就是long类型
        Long query = null;
        try {
            query = (Long) runner.query("select count(id) from orders " + sql, new ScalarHandler(),
                    params.toArray());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query.intValue();
    }

    @Override
    public List<OrdersByPageVO> getOrdersByPage(OrdersByPageBO ordersByPageBO) {
        Map map = getDynamicSqlAndParams(ordersByPageBO);
        String suffix = (String) map.get("sql");
        List params = (List) map.get("params");
        //注意 获取具体数据需要分页，但是查询总条目不需要limit
        String sql = "select * from orders " + suffix + " limit ? offset ?";
        params.add(ordersByPageBO.getPagesize());
        params.add((ordersByPageBO.getCurrentPage() - 1) * ordersByPageBO.getPagesize());
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        List<OrdersByPageVO> ordersVOS = null;
        try {
            ordersVOS = runner.query(sql, new BeanListHandler<OrdersByPageVO>(OrdersByPageVO.class), params.toArray());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ordersVOS;
    }

    @Override
    public int deleteOrder(int id) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            queryRunner.update("delete from orders where id = ?", id);
        } catch (SQLException e) {
            e.printStackTrace();
            return 10000;
        }
        return 0;
    }

    @Override
    public OrderVO getOrder(int id) {
        OrderVO orderVO = new OrderVO();
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            orderVO = queryRunner.query("select id,amount,goodsNum as num,goodsDetailId,stateId as state,goods,goodsId,spec as specName from orders where id = ?", new BeanHandler<>(OrderVO.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return orderVO;
    }

    @Override
    public List<SpecVO> getGoodsSpecs(int goodsId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<SpecVO> goodsSpecs = null;
        try {
            goodsSpecs = queryRunner.query("select * from goods_specs where goodsId = ?", new BeanListHandler<>(SpecVO.class), goodsId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goodsSpecs;
    }

    @Override
    public SpecVO getOrderSpecId(String specName) {
        SpecVO specVO = null;
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            specVO = queryRunner.query("select id from goods_specs where specName = ?", new BeanHandler<>(SpecVO.class), specName);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return specVO;
    }

    @Override
    public String getSpecName(Integer spec) {
        int specId = spec;
        String specName = null;
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            specName = (String) queryRunner.query("select specName from goods_specs where id = ?", new ScalarHandler(), specId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return specName;
    }

    @Override
    public void changeOrder(ChangeOrderBO changeOrderBO) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            queryRunner.update("update orders set goodsNum = ? ,spec = ? ,stateId = ? where id = ?",
                    changeOrderBO.getNum(), changeOrderBO.getSpecName(), changeOrderBO.getState(), changeOrderBO.getId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public User getUser(String token) {
        User user = null;
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            user = runner.query("select * from user where email = ?",new BeanHandler<>(User.class), token);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    @Override
    public GoodsSpecs getGoodsSpecsByDetailId(int goodsDetailId) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        GoodsSpecs goodsSpecs = null;
        try {
            goodsSpecs = runner.query("select * from goods_specs where id = ?",new BeanHandler<>(GoodsSpecs.class), goodsDetailId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goodsSpecs;
    }

    @Override
    public Goods getGoodsById(int goodsId) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        Goods goods = null;
        try {
            goods = runner.query("select * from goods where id = ?", new BeanHandler<>(Goods.class), goodsId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goods;
    }

    @Override
    public void addOrder(AddOrderBO addOrderBO, User user, GoodsSpecs goodsSpecs, Goods goods) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            runner.update("insert into orders values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,now())",
                    user.getId(),user.getNickname(),user.getAddress(),user.getPhone(),goods.getName(),goods.getId(),
                    goodsSpecs.getSpecName(),goodsSpecs.getId(),goodsSpecs.getUnitPrice(),addOrderBO.getNum(),
                    addOrderBO.getAmount(),addOrderBO.getState(),user.getRecipient());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public List<OrdersByStateVO> getOrderByState(int state, Integer id) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        List<OrdersByStateVO> orders = null;
        try {
            if (state == -1) {
                orders = runner.query("select id,goodsDetailId,goodsNum,amount,stateId as state,createtime from orders where userId = ?",
                        new BeanListHandler<>(OrdersByStateVO.class), id);
                for (OrdersByStateVO o : orders) {
                    o.setHasComment(hasComment(getGoodsSpecsByDetailId(o.getGoodsDetailId()).getGoodsId(), id));
                    o.setGoods(GetGoodsByState(o.getGoodsDetailId()));
                }
            } else {
                orders = runner.query("select id,goodsDetailId,goodsNum,amount,stateId as state,createtime from orders where userId = ? and stateId = ?",
                        new BeanListHandler<>(OrdersByStateVO.class), id, state);
                for (OrdersByStateVO o : orders) {
                    o.setHasComment(hasComment(getGoodsSpecsByDetailId(o.getGoodsDetailId()).getGoodsId(), id));
                    o.setGoods(GetGoodsByState(o.getGoodsDetailId()));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return orders;
    }

    private OrdersByState_GoodsVo GetGoodsByState(Integer goodsDetailId) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        OrdersByState_GoodsVo goods = null;
        OrdersByState_GoodsVo tmp;
        try {
            goods = runner.query("select id as goodsDetailId,specName as spec,unitPrice,goodsId as id from goods_specs where id = ?", new BeanHandler<>(OrdersByState_GoodsVo.class), goodsDetailId);
            tmp = runner.query("select img,name from goods where id = ?",new BeanHandler<>(OrdersByState_GoodsVo.class),
                    goods.getId());
            goods.setImg(tmp.getImg());
            goods.setName(tmp.getName());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goods;
    }

    private Boolean hasComment(Integer goodsId, int userId) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            Long count = (Long)runner.query("select count(*) from comment where goodsId = ? and userId = ?",
                    new ScalarHandler(), goodsId, userId);
            if(count.intValue() == 0) return false;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return true;
    }

    @Override
    public int settleAccounts(List<CartAccountsBO> cartList) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        for (CartAccountsBO acc : cartList) {
            try {
                runner.update("update orders set goodsNum= ?, amount = ?, stateId = ? where id = ?",
                        acc.getGoodsNum(), acc.getAmount(), 1, acc.getId());
            } catch (SQLException e) {
                e.printStackTrace();
                return 10000;
            }
        }
        return 0;
    }

    @Override
    public void confirmReceive(int id) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            runner.update("update orders set stateId = 3 where id = ?", id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void sendComment(SendCommentBO sendCommentBO, User user, String specName) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            runner.update("insert into comment values (null,?,?,?,?,now(),?,?)",
                    user.getNickname(), sendCommentBO.getScore(), specName, sendCommentBO.getContent(), user.getId(), sendCommentBO.getGoodsId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
